package util;

import bean.person;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class TouristDao {
    //增加
    public void addTourist(person tourist) throws SQLException {
        Connection conn = DbUtil.getConnection();
        //sql
        String sql="insert into Tourist"+"(id,userName,password,name,sex,birth,nation,phone,place)"+"values("+"'"+tourist.getId()+"'"+","+"'"+tourist.getUserName()+"'"+","+"'"+tourist.getPassword()+"'"+","+"'"+tourist.getName()+"'"+","+"'"+tourist.getSex()+"'"+","+"'"+tourist.getBirth()+"'"+","+"'"+tourist.getNation()+"'"+","+"'"+tourist.getPhone()+"'"+","+"'"+tourist.getPlace()+"'"+")";
        PreparedStatement ptmt = conn.prepareStatement(sql);
        ptmt.execute();

        System.out.println("add tourist success!");
    }

    //删除(id)
    public void delTourist(String userName) throws SQLException {
        Connection conn = DbUtil.getConnection();
        String sql = "delete from Tourist where userName=?";
        //预编译；预编译SQL,减少sql执行
        PreparedStatement ptmt = conn.prepareStatement(sql);
        //传参
        ptmt.setString(1,userName);
        ptmt.execute();
    }

    //更新
    public void updateTourist(String userName,String password,String id,String name,String sex,String birth,String nation,String phone,String place) throws SQLException {
        Connection conn = DbUtil.getConnection();
        String sql = "UPDATE Tourist set name='"+name+"',password='"+password+"',id='"+id+"',sex='"+sex+"',birth='"+birth+"',nation='"+nation+"',phone='"+phone+"',place='"+place+"' where userName='"+userName+"'";
        PreparedStatement ptmt = conn.prepareStatement(sql);
        ptmt.execute();
    }

    //查one(userName)
    public person get(String userName) throws SQLException {
        person tourist = null;
        Connection conn = DbUtil.getConnection();
        String sql = "select * from  Tourist where userName='"+userName+"'";
        PreparedStatement ptmt = conn.prepareStatement(sql);
        ResultSet rs = ptmt.executeQuery();
        while (rs.next()){
            tourist = new person();
            tourist.setUserName(rs.getString("userName"));
            tourist.setPassword(rs.getString("password"));
            tourist.setId(rs.getString("id"));
            tourist.setName(rs.getString("name"));
            tourist.setSex(rs.getString( "sex"));
            tourist.setBirth(rs.getString("birth"));
            tourist.setNation(rs.getString("nation"));
            tourist.setPhone(rs.getString("phone"));
            tourist.setPlace(rs.getString("place"));
        }
        return tourist;
    }

    //列出所有游客
    public List<person> query() throws SQLException {
        Connection conn = DbUtil.getConnection();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("select * from  Tourist");
        List<person> TList = new ArrayList<person>();
        person tourist = null;
        while (rs.next()){
            tourist = new person();
            tourist.setUserName(rs.getString("userName"));
            tourist.setPassword(rs.getString("password"));
            tourist.setId(rs.getString("id"));
            tourist.setName(rs.getString("name"));
            tourist.setSex(rs.getString( "sex"));
            tourist.setBirth(rs.getString("birth"));
            tourist.setNation(rs.getString("nation"));
            tourist.setPhone(rs.getString("phone"));
            tourist.setPlace(rs.getString("place"));
            TList.add(tourist);
        }
        return TList;
    }

    //搜索框
    public List<person> search(String find) throws SQLException {
        Connection conn = DbUtil.getConnection();
        List<person> TList = new ArrayList<person>();
        Statement stmt = conn.createStatement();
        String[] sql = new String[8];
        sql[0] = "select * from  Tourist where userName='"+find+"'";
        sql[1] = "select * from  Tourist where id='"+find+"'";
        sql[2] = "select * from  Tourist where name='"+find+"'";
        sql[3] = "select * from  Tourist where sex='"+find+"'";
        sql[4] = "select * from  Tourist where birth='"+find+"'";
        sql[5] = "select * from  Tourist where nation='"+find+"'";
        sql[6] = "select * from  Tourist where phone='"+find+"'";
        sql[7] = "select * from  Tourist where place='"+find+"'";
        for (int i=0;i<8;i++){
            ResultSet rs = stmt.executeQuery(sql[i]);
            while (rs.next()){
                person tourist = new person();
                tourist.setUserName(rs.getString("userName"));
                tourist.setPassword(rs.getString("password"));
                tourist.setId(rs.getString("id"));
                tourist.setName(rs.getString("name"));
                tourist.setSex(rs.getString( "sex"));
                tourist.setBirth(rs.getString("birth"));
                tourist.setNation(rs.getString("nation"));
                tourist.setPhone(rs.getString("phone"));
                tourist.setPlace(rs.getString("place"));
                TList.add(tourist);
            }
        }
        return TList;
    }
}
